package com.course.dbutil;

public class SqlConstants {
	
	//SolutionDAO
	   
	   public static final String _INSERT_SOLUTION="insert into solution values(?,?,?)";
	   
	   public static final String _GET_QUERY_STATUS="select q.queryid,q.userid,q.qdescription,TO_CHAR(q.qdate,'DD-MM-YYYY'),s.soldate,s.solution ,u.userid,q.qstatus from query q , solution s ,userdetails u where q.queryid=s.queryid and u.loginid=?";
	   
	   public static final String _GET_SOLUTION="select q.queryid,q.userid,q.qdescription,TO_CHAR(q.qdate,'DD-MM-YYYY'),TO_CHAR(s.soldate,'DD-MM-YYYY'),s.solution ,u.userid,q.qstatus from query q , solution s ,userdetails u where q.queryid=s.queryid and s.queryid=? and u.loginid=?";
	   
	   //QueryDAo
	   
	   public static final String _INSERT_QUERY="insert into query values((select nvl(max(queryid),1000)+1 from query),?,?,?,?)";
	   
	   public static final String _GET_QUERY="select queryid,userid,qdescription,qstatus,to_char(qdate,'dd-mm-yy') from query";
	   
	   public static final String _GET_QUERY_AT="select queryid,userid,qdescription,qstatus,TO_CHAR(qdate,'dd-mm-yyyy') from query where queryid=?";
	   

	   
	   
	   //FeedbackDAO
	   
	   public static final String _INSERT_FEEDBACK="insert into feedback values((select nvl(max(fid),100)+1 from feedback),?,?,?,?,?,?,?,?)";
	   
	   public static final String _GET_FEEDBACK="select *from feedback";
	   
	   public static final String _GET_FEEDBACK_AT="select *from feedback where fid=?";
	   
	   public static final String _DELETE_FEEDBACK="delete from feedback where fid=?";
	   
	   //UserDAO
	   
	   public static final String _GET_EMPLOYEE="select USERID,LOGINTYPE,firstname,lastname,to_char(dob),emailid,faxno,photograph,qualification,courseid from userdetails where logintype='faculty'";
       
	   public static final String _GET_COURSE_TITLE="select ctitle from course where courseid=?";
	   public static final String _GET_STUDENTS="select USERID,LOGINTYPE,firstname,lastname,to_char(dob),emailid,faxno,photograph,qualification,courseid from userdetails where logintype='student'";

	   public static final String _DELETE_USER="delete from userdetails where userid=? and courseid=?";
      
	   public static final String _DELETE_STUDENT="delete from userdetails where userid=? and courseid=?";
	   
	   public static final String _MODIFY_PROFILE="{call changeprofile(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
       
	   public static final String _GET_PROFILE="{call showprofile(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
	   
	   //ExamDAO
	   
	   public static final String _INSERT_EXAM_QUESTION="insert into question values((select nvl(max(qno),0)+1 from question),?,?,?,?,?,?)";
	   
	   public static final String _SELECT_QUESTION="select *from question order by qno asc";
	   
	   public static final String _EXAM_SCHEDULE="insert into examschedule values((select nvl(max(examid),100)+1 from examschedule),?,?,?,?)";
	   
	   public static final String _VIEW_SCHEDULE="select *from examschedule";
	   
	   public static final String _INSERT_MARKS="insert into marks values(?,?,(select sysdate from dual))";
	   
	   public static final String _VIEW_MARKS="select *from marks";
	   
	   public static final String _VIEW_MARKS_AT="select *from marks where sname=?";
	   
	   //CourseDAO
	   
	   
	   public static final String _INSERT_COURSE="insert into course values((select nvl(max(courseid),111)+1 from course),?,?,sysdate,?)";
	   
	   public static final String _DELETE_COURSE="delete from course where courseid=?";
	   
	   public static final String _VIEW_COURSE="select courseid,ctitle,cdescription,TO_CHAR(cdate,'DD-MM-YYYY'),cstatus from course";
	   
	   public static final String _VIEW_COURSE_AT="select courseid,ctitle,cdescription,TO_CHAR(cdate,'DD-MM-YYYY'),cstatus from course where courseid=?";
	   
	   public static final String _UPDATE_COURSE="update course set courseid=?,ctitle=?,cdescription=?,cdate=?, cstatus=? where courseid=?";

	   public static final String _INSERT_ANNOUNCEMENT="insert into announcement values((select nvl(max(aid),1000)+1 from announcement),?,?,?,sysdate)";

	   public static final String _VIEW_ANNOUNCEMENT_AT="select aid,courseid,userid,description,TO_CHAR(adate,'DD-MM-YYYY') from announcement where courseid=?";
      
	   public static final String _VIEW_ANNOUNCEMENTS="select aid,courseid,userid,description,TO_CHAR(adate,'DD-MM-YYYY') from announcement ";

	   public static final String _INSERT_MATERIAL="insert into material values((select nvl(max(materialid),101)+1 from material),?,?,?,(select sysdate from dual),?)";

	   public static final String _VIEW_MATERIAL="select materialid,material from material where courseid=?";
       
	   public static final String _GET_FACULTY="select userid ,firstname,lastname,emailid,faxno,courseid,logintype  from userdetails where courseid=? and logintype='faculty'";

	   public static final String _GET_STUDENT="select  userid ,firstname,lastname,emailid,faxno,courseid,logintype  from userdetails where courseid=? and logintype='student'";



}
